﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using FisheryPlatform.Entity;
using FisheryPlatform.DataAccess.DBUtility;
using System.Data;

namespace FisheryPlatform.DataAccess.Product
{
    public class ProductDB
    {
        #region private const
        private const string PARAM_PRODUCT_ID = "@ProductId";
        private const string PARAM_TITLE = "@Title";
        private const string PARAM_CATEGORY_ID = "@CategoryId";
        private const string PARAM_AREA_ID = "@AreaId";
        private const string PARAM_DESCRIPTION = "@Description";
        private const string PARAM_TYPE = "@Type";
        private const string PARAM_MIN_PRICE = "@MinPrice";
        private const string PARAM_MAX_PRICE = "@MaxPrice";
        private const string PARAM_CONTACT = "@Contact";
        private const string PARAM_MOBILE = "@Mobile";
        private const string PARAM_PHONE = "@Phone";
        private const string PARAM_STATUS = "@Status";
        private const string PARAM_CLIENT_IP = "@ClientIp";
        private const string PARAM_USER_ID = "@UserId";
        private const string PARAM_SHOW_CUSTOMER_INFO = "@ShowCustomerInfo";
        private const string PARAM_UPDATE_DATE = "@UpdateDate";
        private const string PARAM_CREATE_DATE = "@CreateDate";

        private static readonly string ConnectionString = SQLHelper.CONNECTION_STRING;
        #endregion

        private static void FillEntityFrom(SqlDataReader reader, ProductEntity obj)
        {
            #region
            if (reader != null && !reader.IsClosed)
            {
                obj.ProductId = reader.IsDBNull(reader.GetOrdinal("ProductId")) ? 0 : reader.GetInt64(reader.GetOrdinal("ProductId"));
                obj.Title = reader.IsDBNull(reader.GetOrdinal("Title")) ? String.Empty : reader.GetString(reader.GetOrdinal("Title"));
                obj.CategoryId = reader.IsDBNull(reader.GetOrdinal("CategoryId")) ? 0 : reader.GetInt64(reader.GetOrdinal("CategoryId"));
                obj.AreaId = reader.IsDBNull(reader.GetOrdinal("AreaId")) ? 0 : reader.GetInt64(reader.GetOrdinal("AreaId"));
                obj.Description = reader.IsDBNull(reader.GetOrdinal("Description")) ? String.Empty : reader.GetString(reader.GetOrdinal("Description"));
                obj.Type = reader.IsDBNull(reader.GetOrdinal("Type")) ? 0 : reader.GetInt64(reader.GetOrdinal("Type"));
                obj.MinPrice = reader.IsDBNull(reader.GetOrdinal("MinPrice")) ? 0 : reader.GetDecimal(reader.GetOrdinal("MinPrice"));
                obj.MaxPrice = reader.IsDBNull(reader.GetOrdinal("MaxPrice")) ? 0 : reader.GetDecimal(reader.GetOrdinal("MaxPrice"));
                obj.Contact = reader.IsDBNull(reader.GetOrdinal("Contact")) ? String.Empty : reader.GetString(reader.GetOrdinal("Contact"));
                obj.Mobile = reader.IsDBNull(reader.GetOrdinal("Mobile")) ? String.Empty : reader.GetString(reader.GetOrdinal("Mobile"));
                obj.Phone = reader.IsDBNull(reader.GetOrdinal("Phone")) ? String.Empty : reader.GetString(reader.GetOrdinal("Phone"));
                obj.Status = reader.IsDBNull(reader.GetOrdinal("Status")) ? ProductStatusEnum.Draft : (ProductStatusEnum)reader.GetByte(reader.GetOrdinal("Status"));
                obj.ClientIp = reader.IsDBNull(reader.GetOrdinal("ClientIp")) ? 0 : reader.GetInt64(reader.GetOrdinal("ClientIp"));
                obj.UserId = reader.IsDBNull(reader.GetOrdinal("UserId")) ? 0 : reader.GetInt64(reader.GetOrdinal("UserId"));
                obj.ShowCustomerInfo = reader.IsDBNull(reader.GetOrdinal("ShowCustomerInfo")) ? Convert.ToByte(0) : reader.GetByte(reader.GetOrdinal("ShowCustomerInfo"));
                obj.UpdateDate = reader.IsDBNull(reader.GetOrdinal("UpdateDate")) ? DateTime.MinValue : reader.GetDateTime(reader.GetOrdinal("UpdateDate"));
                obj.CreateDate = reader.IsDBNull(reader.GetOrdinal("CreateDate")) ? DateTime.MinValue : reader.GetDateTime(reader.GetOrdinal("CreateDate"));
            }
            #endregion
        }

        private static SqlParameter[] GetProductParams(ProductEntity obj)
        {
            #region
            SqlParameter[] dbParams ={					
					 SQLHelper.MakeParam(PARAM_PRODUCT_ID, SqlDbType.BigInt,0,obj.ProductId),
					 SQLHelper.MakeParam(PARAM_TITLE, SqlDbType.NVarChar, 256,obj.Title),					
					 SQLHelper.MakeParam(PARAM_CATEGORY_ID, SqlDbType.BigInt,0,obj.CategoryId),
					 SQLHelper.MakeParam(PARAM_AREA_ID, SqlDbType.BigInt,0,obj.AreaId),
					 SQLHelper.MakeParam(PARAM_DESCRIPTION, SqlDbType.NVarChar, 1024,obj.Description),					
					 SQLHelper.MakeParam(PARAM_TYPE, SqlDbType.BigInt,0,obj.Type),
					 SQLHelper.MakeParam(PARAM_MIN_PRICE, SqlDbType.Decimal,0,obj.MinPrice),
					 SQLHelper.MakeParam(PARAM_MAX_PRICE, SqlDbType.Decimal,0,obj.MaxPrice),
					 SQLHelper.MakeParam(PARAM_CONTACT, SqlDbType.NVarChar, 50,obj.Contact),					
					 SQLHelper.MakeParam(PARAM_MOBILE, SqlDbType.NVarChar, 100,obj.Mobile),					
					 SQLHelper.MakeParam(PARAM_PHONE, SqlDbType.NVarChar, 100,obj.Phone),					
					 SQLHelper.MakeParam(PARAM_STATUS, SqlDbType.TinyInt,0,obj.Status),
					 SQLHelper.MakeParam(PARAM_CLIENT_IP, SqlDbType.BigInt,0,obj.ClientIp),
					 SQLHelper.MakeParam(PARAM_USER_ID, SqlDbType.BigInt,0,obj.UserId),
                     SQLHelper.MakeParam(PARAM_SHOW_CUSTOMER_INFO, SqlDbType.TinyInt,0,obj.ShowCustomerInfo)
			};

            return dbParams;
            #endregion
        }

        public static bool Save(ProductEntity obj)
        {
            #region
            try
            {
                SqlParameter[] dbParams = GetProductParams(obj);

                if (obj.ProductId == 0)
                {
                    obj.ProductId = Convert.ToInt32(SQLHelper.ExecuteScalar(ConnectionString, CommandType.StoredProcedure, "Products_InsertUpdate", dbParams));
                    return obj.ProductId > 0 ? true : false;
                }
                else
                {
                    var affectNum = SQLHelper.ExecuteNonQuery(ConnectionString, CommandType.StoredProcedure, "Products_InsertUpdate", dbParams);
                    return affectNum > 0 ? true : false;
                }
            }
            catch (Exception e)
            {
                Exception ex = new Exception("Product-->InsertOrUpdate-->" + e.Message);

                ExceptionMessageDB.Record(ex);
            }

            return false;
            #endregion
        }

        public static ProductEntity GetByProductId(long productId)
        {
            #region
            SqlDataReader reader = null;
            ProductEntity obj = new ProductEntity();

            try
            {
                SqlParameter[] dbParams = {
                        SQLHelper.MakeParam(PARAM_PRODUCT_ID, SqlDbType.Int, 0, productId)
                    };

                reader = SQLHelper.ExecuteReader(ConnectionString, CommandType.StoredProcedure, "Products_GetByProductId", dbParams);

                if (reader.Read())
                {
                    FillEntityFrom(reader, obj);
                }
            }
            catch (Exception e)
            {
                Exception ex = new Exception("Product-->GetSingleObject-->" + e.Message);

                ExceptionMessageDB.Record(ex);
            }
            finally
            {
                if (reader != null && !reader.IsClosed)
                    reader.Close();
            }

            return obj;
            #endregion
        }

        public static bool Delete(long productId)
        {
            #region
            try
            {
                SqlParameter[] dbParams = {
                        SQLHelper.MakeParam(PARAM_PRODUCT_ID, SqlDbType.Int, 0, productId)
                    };

                return SQLHelper.ExecuteNonQuery(ConnectionString, CommandType.StoredProcedure, "Products_Delete", dbParams) > 0 ? true : false;
            }
            catch (Exception e)
            {
                Exception ex = new Exception("Product-->Delete-->" + e.Message);

                ExceptionMessageDB.Record(ex);
            }
            return false;
            #endregion
        }

        public static bool UpdateStatus(long productId, ProductStatusEnum status)
        {
            #region
            try
            {
                var dbParams = new SqlParameter[]{
                    SQLHelper.MakeParam(PARAM_PRODUCT_ID,SqlDbType.BigInt,0,productId),
                    SQLHelper.MakeParam(PARAM_STATUS,SqlDbType.Int,0,(int) status)
                };
                return SQLHelper.ExecuteNonQuery(ConnectionString, CommandType.StoredProcedure, "Products_UpdateStatus", dbParams) > 0 ? true : false;
            }
            catch (Exception e)
            {
                Exception ex = new Exception("Product-->UpdateStatus-->" + e.Message);

                ExceptionMessageDB.Record(ex);
            }
            return true;
            #endregion
        }

        public static List<ProductEntity> Search(string sqlWhere, int pageIndex, int pageSize, out int pageCount)
        {
            #region
            List<ProductEntity> result = new List<ProductEntity>();
            pageCount = 0;
            try
            {
                if (string.IsNullOrEmpty(sqlWhere))
                {
                    sqlWhere = " 1=1 ";
                }

                SqlParameter[] dbParams =
                {
                    SQLHelper.MakeParam("@sqlWhere", SqlDbType.NVarChar,0,sqlWhere),
                    SQLHelper.MakeParam("@pageIndex",SqlDbType.Int,0,pageIndex),
                    SQLHelper.MakeParam("@pageSize",SqlDbType.Int,0,pageSize),
                    SQLHelper.MakeOutParam("@pageCount", SqlDbType.Int,0,0)                    
                };
                using (SqlDataReader reader = SQLHelper.ExecuteReader(ConnectionString, CommandType.StoredProcedure, "Products_Search", dbParams))
                {
                    while (reader.Read())
                    {
                        ProductEntity model = new ProductEntity();
                        FillEntityFrom(reader, model);
                        result.Add(model);
                    }
                }
                pageCount = int.Parse(dbParams[3].Value.ToString());
            }
            catch (Exception e)
            {
                Exception ex = new Exception("Product-->Search-->" + e.ToString());
                ExceptionMessageDB.Record(ex);
            }
            return result;
            #endregion
        }
    }
}
